package com.dorm.mapper;

import com.dorm.entity.Building;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface BuildingMapper {
    @Select("SELECT b.*, " +
            "(SELECT COUNT(DISTINCT r.id) FROM rooms r WHERE r.building_id = b.id) as total_rooms, " +
            "(SELECT COUNT(DISTINCT r.id) FROM rooms r " +
            "WHERE r.building_id = b.id AND EXISTS (" +
            "    SELECT 1 FROM hygiene_records h WHERE h.room_id = r.id" +
            ")) as checked_rooms, " +
            "(SELECT AVG(h.score) FROM hygiene_records h " +
            "INNER JOIN rooms r ON h.room_id = r.id " +
            "WHERE r.building_id = b.id) as average_score, " +
            "(SELECT MAX(h.check_time) FROM hygiene_records h " +
            "INNER JOIN rooms r ON h.room_id = r.id " +
            "WHERE r.building_id = b.id) as last_check_time, " +
            "(SELECT COALESCE(SUM(r.current_occupancy) * 100.0 / NULLIF(SUM(r.capacity), 0), 0) " +
            "FROM rooms r WHERE r.building_id = b.id) as occupancy_rate " +
            "FROM buildings b")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "buildingName", column = "building_name"),
        @Result(property = "floors", column = "floors"),
        @Result(property = "description", column = "description"),
        @Result(property = "totalRooms", column = "total_rooms"),
        @Result(property = "checkedRooms", column = "checked_rooms"),
        @Result(property = "averageScore", column = "average_score"),
        @Result(property = "lastCheckTime", column = "last_check_time"),
        @Result(property = "occupancyRate", column = "occupancy_rate")
    })
    List<Building> findAll();

    @Select("SELECT b.*, " +
            "(SELECT COUNT(*) FROM rooms r WHERE r.building_id = b.id) as total_rooms, " +
            "(SELECT COUNT(*) FROM (" +
            "    SELECT DISTINCT r.id " +
            "    FROM rooms r " +
            "    INNER JOIN hygiene_records h ON r.id = h.room_id " +
            "    WHERE r.building_id = b.id" +
            ") as checked) as checked_rooms, " +
            "(SELECT AVG(h.score) FROM hygiene_records h " +
            "INNER JOIN rooms r ON h.room_id = r.id " +
            "WHERE r.building_id = b.id) as average_score, " +
            "(SELECT MAX(h.check_time) FROM hygiene_records h " +
            "INNER JOIN rooms r ON h.room_id = r.id " +
            "WHERE r.building_id = b.id) as last_check_time " +
            "FROM buildings b WHERE b.id = #{id}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "buildingName", column = "building_name"),
        @Result(property = "floors", column = "floors"),
        @Result(property = "description", column = "description"),
        @Result(property = "totalRooms", column = "total_rooms"),
        @Result(property = "checkedRooms", column = "checked_rooms"),
        @Result(property = "averageScore", column = "average_score"),
        @Result(property = "lastCheckTime", column = "last_check_time")
    })
    Building findById(Long id);

    @Insert("INSERT INTO buildings(building_name, floors, description) " +
            "VALUES(#{buildingName}, #{floors}, #{description})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Building building);

    @Update("UPDATE buildings SET building_name = #{buildingName}, " +
            "floors = #{floors}, description = #{description} WHERE id = #{id}")
    int update(Building building);

    @Delete("DELETE FROM buildings WHERE id = #{id}")
    int deleteById(Long id);
} 